from typing import Generator
from urllib import parse

from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from common.database import CustomQuery

from setting import config


def apollo_mysql():
    db_host = config.APOLLO_DB_HOST
    db_user = config.APOLLO_DB_USER
    db_password = config.APOLLO_DB_PASSWORD
    db_name = config.APOLLO_DB_NAME
    return f'mysql+pymysql://{db_user}:{parse.quote_plus(db_password)}@{db_host}:3306/{db_name}?charset=utf8mb4'


db_url = apollo_mysql()
base = declarative_base()
engine = create_engine(url=db_url, pool_size=10, pool_recycle=7200, pool_timeout=30)
metadata = MetaData(bind=engine)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine, query_cls=CustomQuery)


async def get_db() -> Generator:
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

#
#
# def pro_apollo_mysql():
#     db_host = config.PRO_APOLLO_DB_HOST
#     db_user = config.PRO_APOLLO_DB_USER
#     db_password = config.PRO_APOLLO_DB_PASSWORD
#     db_name = config.PRO_APOLLO_DB_NAME
#     return f'mysql+pymysql://{db_user}:{parse.quote_plus(db_password)}@{db_host}:3306/{db_name}?charset=utf8mb4'
#
#
# pro_db_url = pro_apollo_mysql()
# pro_base = declarative_base()
# pro_engine = create_engine(url=pro_db_url, pool_size=10, pool_recycle=7200, pool_timeout=30)
# pro_metadata = MetaData(bind=pro_engine)
#
#
# def get_apollo_env(env):
#     if env == 'fat':
#         SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=fat_engine, query_cls=CustomQuery)
#     else:
#         SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=pro_engine, query_cls=CustomQuery)
#     return SessionLocal()


# class ProApolloNamespace(pro_base, SessionMixin):
#     """映射已有的数据库，传入三个参数：数据库名称、metadata（绑定接口）、autoload=True"""
#     __table__ = Table('namespace', pro_metadata, autoload=True)
#
#
# class ProApolloItem(pro_base, SessionMixin):
#     """映射已有的数据库，传入三个参数：数据库名称、metadata（绑定接口）、autoload=True"""
#     __table__ = Table('item', pro_metadata, autoload=True)


# def apollo_search(db: Session, env, app_name=None, key=None, value=None) -> List[Dict]:
#     if env == 'pro':
#         sql = db.query(ProApolloNamespace). \
#             outerjoin(ProApolloItem, ProApolloNamespace.Id == ProApolloItem.NamespaceId). \
#             with_entities(ProApolloNamespace.AppId.label('app_id'),
#                           ProApolloNamespace.ClusterName.label('cluster_name'),
#                           ProApolloItem.Key.label('key'),
#                           ProApolloItem.Value.label('value'),
#                           ProApolloItem.DataChange_CreatedTime.label('created_time'),
#                           ProApolloItem.DataChange_LastTime.label('last_time'))
#
#         if app_name:
#             sql = sql.filter(FatApolloNamespace.AppId == app_name)
#
#         if key and value:
#             return sql.filter(
#                 and_(FatApolloNamespace.IsDeleted == 0, FatApolloItem.IsDeleted == 0,
#                      FatApolloItem.Key.like(f"%{key}%"),
#                      FatApolloItem.Value.like(f"%{value}%"))).order_by("app_id").to_dict()
#
#         if key:
#             return sql.filter(
#                 and_(FatApolloNamespace.IsDeleted == 0, FatApolloItem.IsDeleted == 0,
#                      FatApolloItem.Key.like(f"%{key}%"),
#                      FatApolloItem.Value != '')).to_dict()
#
#         if value:
#             return sql.filter(
#                 and_(FatApolloNamespace.IsDeleted == 0, FatApolloItem.IsDeleted == 0, FatApolloItem.Key != '',
#                      FatApolloItem.Value.like(f"%{value}%"))).to_dict()
#
#     if env == 'fat':
#         sql = db.query(FatApolloNamespace). \
#             outerjoin(FatApolloItem, FatApolloNamespace.Id == FatApolloItem.NamespaceId). \
#             with_entities(FatApolloNamespace.AppId.label('app_id'),
#                           FatApolloNamespace.ClusterName.label('cluster_name'),
#                           FatApolloItem.Key.label('key'),
#                           FatApolloItem.Value.label('value'),
#                           FatApolloItem.DataChange_CreatedTime.label('created_time'),
#                           FatApolloItem.DataChange_LastTime.label('last_time'))
#
#         if app_name:
#             sql = sql.filter(FatApolloNamespace.AppId == app_name)
#
#         if key and value:
#             return sql.filter(
#                 and_(FatApolloNamespace.IsDeleted == 0, FatApolloItem.IsDeleted == 0,
#                      FatApolloItem.Key.like(f"%{key}%"),
#                      FatApolloItem.Value.like(f"%{value}%"))).to_dict()
#
#         if key:
#             return sql.filter(
#                 and_(FatApolloNamespace.IsDeleted == 0, FatApolloItem.IsDeleted == 0,
#                      FatApolloItem.Key.like(f"%{key}%"),
#                      FatApolloItem.Value != '')).to_dict()
#
#         if value:
#             return sql.filter(
#                 and_(FatApolloNamespace.IsDeleted == 0, FatApolloItem.IsDeleted == 0, FatApolloItem.Key != '',
#                      FatApolloItem.Value.like(f"%{value}%"))).to_dict()
#
#     return []


# if __name__ == '__main__':
#     """
#     select *
#     from namespace
#              left join item on namespace.Id = item.NamespaceId
#     where namespace.IsDeleted = 0
#       AND item.IsDeleted = 0
#       AND ITEM.`Key` != ''
#       AND item.Value LIKE '%wld.net%';
#     """
#
#     # 创建事务
#     SessionLocal = sessionmaker(bind=fat_engine, query_cls=CustomQuery)
#     session = SessionLocal()
#
#     from pprint import pprint
#
#     #
#     pprint(apollo_search(session, env='fat', key="welab.privacy.root.url"))
# print(db.query(ApolloNamespace).limit(10).to_dict())
